package DBLayer;

import ModelLayer.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DBProduct implements IFDBProduct {
	private Connection con;
	private DBSupplier dbSup;


	public DBProduct()
	{
		con = DBConnection.getInstance().getDBcon();
		// DBSupplier used to build the supplier obj when fetching products.
		dbSup = new DBSupplier();
	}

	public ArrayList<Product> getAllProducts()
	{
		return miscWhere("");
	}
	
	public ArrayList<Product> getAllProducts(String name)
	{   
		String wClause = "  name LIKE '%" + name + "%'";
		return miscWhere(wClause);
	}

	public Product searchProduct(int id)
	{   
		String wClause = "  productId = '" + id + "'";
		return singleWhere(wClause);
	}
	public Product searchProduct(String name)
	{   
		String wClause = "  name = '" + name + "'";
		return singleWhere(wClause);
	}

	public int insertProduct(Product p) throws Exception
	{
		//call to get the next id number
		int nextID = GetMax.getMaxId("Select max(productId) from product");
		nextID = nextID + 1;
		System.out.println("next id = " +  nextID);
		int rc = -1;
		String insertquery="INSERT INTO product(productId, name, purchasePrice, salesPrice," +
				" rentPrice, countryOfOrigin, minStoct, StockAmount, supplierId";
		String valuequery = " VALUES('"+
				nextID  + "','"  +
				p.getName() + "','"  +
				p.getPurchasePrice() + "','"  +
				p.getSalesPrice() + "','"  +
				p.getRentPrice() + "','"  +
				p.getCountryOfOrigin() + "','"  +
				p.getMinStock() + "','"  +
				p.getStockAmount() + "','"  +
				p.getSupplier().getId() +"'";

		if(p instanceof Clothing)
		{
			Clothing c = (Clothing) p;
			insertquery += ", size, colour ";
			valuequery += ",'"+ c.getSize()+"','"+
					c.getColour()+"'";
		}
		if(p instanceof Equipment)
		{
			Equipment e = (Equipment) p;
			insertquery += ", type, description ";
			valuequery += ",'"+ e.getType()+"','"+
					e.getDescription()+"'";
		}		
		if(p instanceof GunReplicas)
		{
			GunReplicas g = (GunReplicas) p;
			insertquery += ", fabric, calibre ";
			valuequery += ",'"+ g.getFabric()+"','"+
					g.getCalibre()+"'";
		}

		String query = insertquery+") "+valuequery+")"; 


		System.out.println("insert : " + query);
		try
		{ // insert new supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Product not created");
			throw new Exception ("Supplier is not inserted correct");
		}
		return(rc);
	}

	public int deleteProduct(Product p)
	{
		int rc=-1;

		String query="DELETE FROM product WHERE productId = '" +
				p.getProductId() + "'";
		System.out.println(query);
		try
		{ // delete from supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in product db: "+ex);
		}
		return(rc);
	}

	public int updateProduct(Product p)
	{
		int rc=-1;

		String query="UPDATE product SET "+
				"name ='"+ p.getName()+"', "+
				"purchasePrice='"+ p.getPurchasePrice()+"', "+
				"salesPrice='"+ p.getSalesPrice()+"', "+
				"rentPrice='"+ p.getRentPrice()+"', "+
				"countryOfOrigin='"+ p.getCountryOfOrigin()+"', "+
				"minStoct='"+ p.getMinStock()+"', "+
				"StockAmount='"+ p.getStockAmount()+"', "+
				"supplierId='"+ p.getSupplier().getId() +"' ";
		if(p instanceof Clothing)
		{
			Clothing c = (Clothing) p;
			query += ", size ='"+ c.getSize()+"', "+
					"colour ='"+ c.getColour()+"'";
		}
		if(p instanceof Equipment)
		{
			Equipment e = (Equipment) p;
			query += ", type ='"+ e.getType()+"', "+
					"description ='"+ e.getDescription()+"'";
		}		
		if(p instanceof GunReplicas)
		{
			GunReplicas g = (GunReplicas) p;
			query += ", fabric ='"+ g.getFabric()+"', "+
					"calibre ='"+ g.getCalibre()+"'";
		}		

		query += " WHERE productId = '"+ p.getProductId() + "'";
		try
		{ // update product
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in employee db: "+ex);
		}
		return(rc);
	}

	private ArrayList<Product> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();	

		String query =  buildQuery(wClause);

		try
		{ // read the products from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				Product prodObj = new Product();
				prodObj = buildProduct(results);	
				list.add(prodObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	private Product singleWhere(String wClause)
	{
		ResultSet results;
		Product prodObj = new Product();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try
		{ // read the product from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				prodObj = buildProduct(results);
				stmt.close();
			}
			else{ //no product was found
				prodObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return prodObj;
	}

	private Product buildProduct(ResultSet results) {
		Product prodObj = new Product();
		try 
		{
			if(results.getObject("size") != null)
			{
				prodObj = buildProductCloth(results);
			}
			if(results.getObject("type") != null)
			{
				prodObj = buildProductEquip(results);
			}
			if(results.getObject("fabric") != null)
			{
				prodObj = buildProductGun(results);
			}

		}
		catch (SQLException e) {
			System.out.println("error in building the Product object");
		}
		return prodObj;
	}

	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT productId, name, purchasePrice, salesPrice," +
				" rentPrice, countryOfOrigin, minStoct, StockAmount, size, colour," +
				" fabric, calibre, type, description, supplierId FROM product";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;


		return query;
	}
	//productId, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, StockAmount, size, colour, type, description, fabric, calibre, supplierId
	private Clothing buildProductCloth(ResultSet results)
	{   Clothing cloObj = new Clothing();
	try
	{ // the columns from the table product are used
		cloObj.setProductId(results.getInt("productId"));
		cloObj.setName(results.getString("name"));
		cloObj.setPurchasePrice(results.getDouble("purchasePrice"));
		cloObj.setSalesPrice(results.getDouble("salesPrice"));
		cloObj.setRentPrice(results.getDouble("rentPrice"));
		cloObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
		cloObj.setMinStock(results.getInt("minStoct"));
		cloObj.setStockAmount(results.getInt("stockAmount"));
		cloObj.setSupplier(dbSup.searchSupplier(results.getInt("supplierId")));
		cloObj.setSize(results.getString("size"));
		cloObj.setColour(results.getString("colour"));

	}
	catch(Exception e)
	{
		System.out.println("error in building the Product object");
	}
	return cloObj;
	}
	private Equipment buildProductEquip(ResultSet results)
	{   Equipment equObj = new Equipment();
	try
	{ // the columns from the table product  are used
		equObj.setProductId(results.getInt("productId"));
		equObj.setName(results.getString("name"));
		equObj.setPurchasePrice(results.getDouble("purchasePrice"));
		equObj.setSalesPrice(results.getDouble("salesPrice"));
		equObj.setRentPrice(results.getDouble("rentPrice"));
		equObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
		equObj.setMinStock(results.getInt("minStoct"));
		equObj.setStockAmount(results.getInt("stockAmount"));
		equObj.setSupplier(dbSup.searchSupplier(results.getInt("supplierId")));
		equObj.setType(results.getString("type"));
		equObj.setDescription(results.getString("description"));

	}
	catch(Exception e)
	{
		System.out.println("error in building the Product object");
	}
	return equObj;
	}
	private GunReplicas buildProductGun(ResultSet results)
	{   GunReplicas gunObj = new GunReplicas();
	try
	{ // the columns from the table product  are used
		gunObj.setProductId(results.getInt("productId"));
		gunObj.setName(results.getString("name"));
		gunObj.setPurchasePrice(results.getDouble("purchasePrice"));
		gunObj.setSalesPrice(results.getDouble("salesPrice"));
		gunObj.setRentPrice(results.getDouble("rentPrice"));
		gunObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
		gunObj.setMinStock(results.getInt("minStoct"));
		gunObj.setStockAmount(results.getInt("stockAmount"));
		gunObj.setSupplier(dbSup.searchSupplier(results.getInt("supplierId")));
		gunObj.setFabric(results.getString("fabric"));
		gunObj.setCalibre(results.getString("calibre"));

	}
	catch(Exception e)
	{
		System.out.println("error in building the Product object");
	}
	return gunObj;
	}
}
